<?php

if (! defined ( 'BASEPATH' ))
	exit ( 'No direct script access allowed' );

class Model_Cover_Gril extends CI_Model {
	
	private $m_cover_gril_db = null;
	private $m_friend_db = null;
	
	public function __construct() {
		parent::__construct ();
		require(APPPATH.'config/database.php');
		$this->m_cover_gril_db = $this->load->database ( ACTIVE_COMPETITION, true );
	}
	public function get_period_by_time( $time ) {
		$sql = "SELECT period, start_time, end_time, number FROM cover_gril_competition WHERE start_time < '$time' AND end_time > '$time' ";
//		log_message('debug', "database operation get_period_by_time $sql");
		
		$query = $this->m_cover_gril_db->query( $sql );
		if ($query->num_rows() > 0) {
			return $query->row(0);
		}
		else {
			return null;
		}
	}
	public function get_current_period() {
		$sql = "SELECT period, start_time, end_time, number FROM cover_gril_competition WHERE state = 1 ORDER BY end_time DESC LIMIT 0, 1 ";
//		log_message('debug', "database operation get_current_period $sql");
		$query = $this->m_cover_gril_db->query( $sql );
		if ($query->num_rows() > 0) {
			return $query->row(0);
		}
		else {
			return null;
		}
	}
	public function get_last_period() {
		$sql = "SELECT period, start_time, end_time, number FROM cover_gril_competition WHERE state = 2 ORDER BY end_time DESC LIMIT 0, 1 ";
//		log_message('debug', "database operation get_last_period $sql");
		$query = $this->m_cover_gril_db->query( $sql );
		if ($query->num_rows() > 0) {
			return $query->row(0);
		}
		else {
			return null;
		}
	}
	public function get_reward($userid) {
		$sql = "SELECT a.userid, a.period, cover_gril_competition.start_time, cover_gril_competition.end_time, a.number_vote, a.ranking 
				FROM (SELECT userid, period, number_vote, ranking FROM cover_gril_history WHERE userid = $userid AND has_get_reward = 0 ) as a
					INNER JOIN cover_gril_competition ON a.period = cover_gril_competition.period
				ORDER BY period ASC LIMIT 0, 1";
		$query = $this->m_cover_gril_db->query( $sql );
		if ($query->num_rows() > 0) {
			return $query->row(0);
		}
		else {
			return null;
		}
	}
	public function get_login_reward($userid) {
		require_once (BASEPATH . 'helpers/date_helper.php');
		$now = strftime ( "%Y-%m-%d %H:%M:%S", now () );
		
		$sql = "SELECT login_reward_time FROM cover_gril WHERE userid = $userid ";
		$query = $this->m_cover_gril_db->query( $sql );
		if ($query->num_rows() > 0) {
			$last_time = $query->row(0)->login_reward_time;
			
			$now1 = strtotime ( "+8 hour", $now );
			$last_time1 = strtotime ( "+8 hour", $last_time );
			$w1 = strftime ( '%w', $now1 );
			$w2 = strftime ( '%w', $last_time1 );
			if ($w1 > $w2) {
				$sql = "UPDATE cover_gril SET login_reward_time = '$now', number_vote = number_vote + 5,  WHERE userid = $userid ";
				$this->m_cover_gril_db->query( $sql );
				return 10;
			}
			else {
				return null;
			}
		}
		else {
			return null;
		}
	}
	public function confirm_reward($userid, $period) {
		$sql = "UPDATE cover_gril_history SET has_get_reward = 1 WHERE userid = $userid AND period = $period";
		log_message('debug', "confirm_reward $sql");
		$this->m_cover_gril_db->query( $sql );
		if ($this->m_cover_gril_db->affected_rows () > 0) {
			return true;
		}
		else {
			log_message('error', "database operation confirm_reward failed $userid, $period");
			return false;
		}
	}
	public function cover_gril_weak($userid) {
		$sql = "UPDATE cover_gril SET number_weak = number_weak+1 WHERE userid = $userid";
		log_message('debug', "database operation cover_gril_weak $userid");
		$this->m_cover_gril_db->query( $sql );
	}
	public function cover_gril_vote($voted_userid, $time) {
		$result = null;
		$sql = "call proc_vote( $voted_userid, '$time', @num_ticket, @ranking )";
		
		$query = $this->m_cover_gril_db->query ( $sql );
		$query1 = $this->m_cover_gril_db->query ( "select @num_ticket as num_ticket" );
		$query2 = $this->m_cover_gril_db->query ( "select @ranking as ranking" );
		$num_ticket = $query1->row ( 0 )->num_ticket;
		$ranking = $query2->row ( 0 )->ranking;
		if ($query && $num_ticket && $ranking) {
			$result->num_ticket = $num_ticket;
			$result->ranking = $ranking;
			log_message ( 'debug', "database operation cover_gril_vote return true  $result->num_ticket  $result->ranking" );
			return $result;
		} else {
			log_message ( 'error', "database operation cover_gril_vote error $sql" );
			return null;
		}
	}
	public function add_user( $userid_, $username_ ) {
		$int_max = (1 << 31 - 1);
		$sql = "INSERT INTO user (userid, username, cover_gril_best_ranking, pk_best_ranking) SELECT $userid_, ".$this->m_cover_gril_db->escape($username_)." , $int_max, $int_max FROM dual WHERE not exists (SELECT userid FROM user WHERE userid = $userid_)";
		log_message('database operation add_user ', $sql);
		$query = $this->m_cover_gril_db->query( $sql );
	}
	public function get_newest_cover_gril_period() {
		$sql = "SELECT * FROM cover_gril_competition ORDER BY period DESC LIMIT 0, 2 ";
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query;
		} else {
			log_message ( 'error', "database operation get_newest_cover_gril_period $sql " );
			return null;
		}
	}
	public function add_new_cover_gril_period($new_period) {
		$sql = "INSERT INTO cover_gril_competition (period, state, start_time, end_time, number) 
				VALUES ($new_period->period, 1, '$new_period->start_time', '$new_period->end_time', $new_period->number)";
		log_message('debug', "add_new_cover_gril_period '$sql' ");
		$query = $this->m_cover_gril_db->query( $sql ) ;
		if ($this->m_cover_gril_db->affected_rows () > 0) {
			log_message ( 'debug', "database operation add_new_cover_gril_period success $sql" );
			return TRUE;
		} else {
			log_message ( 'error', "database operation add_new_cover_gril_period $sql");
			return FALSE;
		}
	}
	public function cover_gril_change_period($next_period, $current_period) {

		log_message ( 'info', "database operation cover_gril_change_period" );
		$sql = "call proc_change_period( $next_period, $current_period ) ";
		log_message ( 'info', "database operation cover_gril_change_period success $sql" );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_list_by_random($period, $start, $num) {
	
		$sql = "SELECT result.* FROM
		(SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg
		FROM cover_gril WHERE period = $period AND shield = 0) AS result
		INNER JOIN ( SELECT ( ROUND( RAND() * ( MAX( userid ) - MIN( userid) ) ) + MIN( userid)*0.5 ) AS random FROM cover_gril WHERE period = $period  ) AS c
		WHERE result.userid >= c.random
		ORDER BY result.userid ASC
		LIMIT 0, $num ";
	
	log_message ( 'debug', "database operation get_cover_gril_by_random  " );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	
	public function get_cover_gril_list_by_latest($period, $start, $num) {
		
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril WHERE period = $period AND shield = 0
				ORDER BY create_time DESC 
				LIMIT $start, $num ";
		log_message ( 'debug', "database operation get_cover_gril_list_by_latest_vote  " );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_list_by_mostest($period, $start, $num) {
	
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
		FROM cover_gril WHERE period = $period AND shield = 0
		ORDER BY number_vote DESC
		LIMIT $start, $num ";
		log_message ( 'debug', "database operation get_cover_gril_by_mostest_vote " );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_list_by_hottest($period, $start, $num, $time) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg, 
					LOG(number_vote+2)*(60.0/MINUTE(TIMEDIFF('$time', create_time)) + 30.0/MINUTE(TIMEDIFF('$time', latest_time_vote)) ) as hot FROM cover_gril WHERE period = $period AND shield = 0
		ORDER BY hot DESC
		LIMIT $start, $num ";
		log_message ( 'debug', "database operation get_cover_gril_by_mostest_vote $sql" );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_list_by_attention($userid, $period, $start, $num) {
	
		$sql = "SELECT invitee FROM friend WHERE inviter = $userid";
		$query = $this->m_cover_gril_db->query($sql);
	
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril WHERE period = $period AND shield = 0 AND ";
		$num = $query->num_rows();
		if ($num > 0) {
			$sql.= ' userid IN ( ';
			
			foreach ($query->result_array() as $row) {
				$sql.=($row['invitee'].',');
			}
			$sql = substr($sql, 0, -1);
			$sql.= ' ) ';
		}
		else {
			$sql.= ' 0 ';
		}
		$sql.=" ORDER BY latest_time_vote DESC LIMIT $start, $num ";

		log_message('debug', "database operation get_cover_gril_list_by_attention " );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_list_by_fans($userid, $period, $start, $num) {
	
		$sql = "SELECT inviter FROM friend WHERE invitee = $userid";
		$query = $this->m_cover_gril_db->query($sql);
	
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril WHERE period = $period AND shield = 0 AND ";
		$num = $query->num_rows();
		if ($num > 0) {
			$sql.= ' userid IN (';
			foreach ($query->result_array() as $row) {
				$sql.=($row['inviter'].',');
			}
			$sql = substr($sql, 0, -1);
			$sql.=' ) ';
		}
		else {
			$sql.= ' 0 ';
		}
		$sql.=" ORDER BY latest_time_vote DESC LIMIT $start, $num ";

		log_message('debug', "database operation get_cover_gril_list_by_fans " );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	
	public function get_cover_gril_last_period($start, $num) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril_last
				ORDER BY number_vote DESC
				LIMIT $start, $num";
		log_message('debug', "database operation get_cover_gril_last_period " );
		$query = $this->m_cover_gril_db->query( $sql );
		return $query;
	}
	public function get_cover_gril_total_person($period) {
		$sql = "SELECT number FROM cover_gril_competition WHERE period = $period";
		$query = $this->m_cover_gril_db->query($sql);
		if ($query->num_rows() > 0) {
			if ($query->num_rows () > 1) {
				log_message ( 'error', "database operation get_cover_gril_total_person result more than 1  $sql" );
			}
			return $query->row(0);
		}
		else {
			log_message ( 'error', "database operation get_cover_gril_total_person have 0 line  $sql" );
			return null;
		}
	}
	
	public function is_cover_gril_exist($userid, $period) {
		$sql = "SELECT userid FROM cover_gril WHERE userid = $userid AND period = $period ";
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 0) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
	public function add_cover_gril($userid, $period, $create_time, $plist, $md5sum, $bg) {
		$int_max = (1 << 31 - 1);
		$sql = "SELECT username FROM user WHERE userid = $userid";
		$query = $this->m_cover_gril_db->query ( $sql );
		$username = $query->row( 0 )->username;
		$sql = "INSERT INTO cover_gril( userid, username, period, number_vote, latest_time_vote, create_time, login_reward_time, ranking, plist, bg, md5sum ) VALUES ( $userid, '$username', $period, 0, '$create_time', '$create_time', '$create_time', $int_max, '$plist', $bg, '$md5sum' )";
		$this->m_cover_gril_db->query ( $sql );
//		log_message ( 'debug', "database operation add_cover_gril  $sql" );
		if ($this->m_cover_gril_db->affected_rows () > 0) {
			$sql = "UPDATE user SET cover_gril_count = cover_gril_count + 1 WHERE userid = $userid";
			$this->m_cover_gril_db->query ( $sql );
			$sql = "UPDATE cover_gril_competition SET number = number + 1 WHERE period = $period";
			$this->m_cover_gril_db->query ( $sql );
			return TRUE;
		} else {
			log_message ( 'error', "database operation add_cover_gril failed $sql" );
			return FALSE;
		}
	}
	
	public function get_cover_gril($userid, $current_period) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril WHERE userid = $userid AND period = $current_period
				ORDER BY period DESC";
		log_message ( 'debug', "database operation get_cover_gril $sql" );
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 2) {
			log_message ( 'error', "database operation get_cover_gril result more than 2  $sql" );
		}
		return $query;
	}
	public function get_cover_gril_list_by_name($username, $period, $start, $num) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril 
				WHERE period = $period AND username LIKE '%" . $this->m_cover_gril_db->escape_like_str ( $username ) . "%' AND shield = 0
				LIMIT $start, $num";
		log_message ( 'debug', "database operation get_cover_gril_list_by_name" );
		$query = $this->m_cover_gril_db->query ( $sql );
		return $query;
	}
	public function get_cover_gril_data($userid) {
		$sql = "SELECT cover_gril_count, cover_gril_best_score, cover_gril_best_ranking, cover_gril_gold, cover_gril_silver, cover_gril_bronze FROM user WHERE userid = $userid ";
		log_message ( 'debug', "database operation get_cover_gril_data $sql" );
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->row(0);
		}
		else {
			return null;
		}
	}
	public function get_current_period_by_userid( $userid_ ) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
				FROM cover_gril WHERE userid = $userid_
				ORDER BY period DESC";
		log_message ( 'debug', "database operation get_current_period_by_userid " );
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 2) {
			log_message ( 'error', "database operation get_current_period_by_userid result more than 2  $sql" );
		}
		return $query;
	}
	public function get_last_period_by_userid( $userid_ ) {
		$sql = "SELECT userid, username, period, number_vote, latest_time_vote, create_time, ranking, plist, md5sum, bg 
		FROM cover_gril_last WHERE userid = $userid_
		ORDER BY period DESC";
		log_message ( 'debug', "database operation get_last_period_by_userid " );
		$query = $this->m_cover_gril_db->query ( $sql );
		if ($query->num_rows () > 2) {
			log_message ( 'error', "database operation get_last_period_by_userid result more than 2  $sql" );
		}
		return $query;
	}
}
